package com.wangdao.mall.dao;

import com.alibaba.druid.util.StringUtils;
import com.wangdao.mall.model.*;
import com.wangdao.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:HaiyangTian
 * @Description:
 * @Date: Created in 18:54 2020/5/3
 * @Modified By:
 */
public class OrderDaoImp implements OrderDao {
    /**
     * 计算应该获取到的分页数据
     * @param param
     * @return
     */
    @Override
    public Integer getTotal(OrderRequestParam param) {
        Map<String,Object> map = getDynamicSqlParams(param);
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String sql = (String) map.get("sql");
        List params = (List) map.get("params");
        Long total = null;
        try {
            total = runner.query("select count(id) from orders " + sql,
                    new ScalarHandler<>(), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total.intValue();
    }

    /**
     * 获取动态的sql语句
     * @param param
     * @return
     */
    private Map<String, Object> getDynamicSqlParams(OrderRequestParam param) {
        String baseSql = "where 1 = 1 ";
        List<Object> sqlParams = new ArrayList<>();
        if (param.getState() != -1){
            baseSql = baseSql + "and stateId = ? ";
            sqlParams.add(param.getState());
        }
        if (!StringUtils.isEmpty(param.getMoneyLimit1())){
            baseSql = baseSql + "and amount <= ? ";
            sqlParams.add(Double.parseDouble(param.getMoneyLimit1()));
        }
        if (!StringUtils.isEmpty(param.getMoneyLimit2())){
            baseSql = baseSql + "and amount >= ? ";
            sqlParams.add(Double.parseDouble(param.getMoneyLimit2()));
        }
        if (!StringUtils.isEmpty(param.getGoods())){
            baseSql = baseSql + "and goods like ? ";
            sqlParams.add("%" + param.getGoods() + "%");
        }
        if (!StringUtils.isEmpty(param.getAddress())){
            baseSql = baseSql + "and address like ? ";
            sqlParams.add("%" + param.getAddress() + "%");
        }
        if (!StringUtils.isEmpty(param.getName())){
            baseSql = baseSql + "and name like ? ";
            sqlParams.add("%" + param.getName() + "%");
        }
        if (!StringUtils.isEmpty(param.getId())){
            baseSql = baseSql + "and id = ? ";
            sqlParams.add(param.getId());
        }
        Map<String,Object> map = new HashMap<>();
        map.put("sql",baseSql);
        map.put("params",sqlParams);
        return map;
    }

    /**
     * 获取orders表里的数据
     * @param param
     * @return
     */
    @Override
    public List<Order> ordersByPage(OrderRequestParam param) {
        Map<String,Object> map = getDynamicSqlParams(param);
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String sql = (String) map.get("sql");
        List params = (List) map.get("params");
        params.add(param.getPagesize());
        params.add((param.getCurrentPage() - 1) * param.getPagesize());
        List<Order> orders = null;
        try {
            orders = runner.query("select * from orders " + sql + "limit ? offset ?",
                    new BeanListHandler<Order>(Order.class),
                    params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    /**
     * 通过id查询orders表得数据
     * @param id
     */
    @Override
    public Order getOrder(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Order order = null;
        try {
            order = runner.query("select * from orders where id = ?",
                    new BeanHandler<>(Order.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return order;
    }

    /**
     * 获得对应商品的规格列表
     * @param goodsDetailId
     * @return
     */
    @Override
    public List<OrderSpec> getSpecList(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<OrderSpec> specs = null;
        try {
            Integer goodsId = runner.query("select goodsId from spec where id = ?",
                    new ScalarHandler<>(), goodsDetailId);
            specs = runner.query("select * from spec where goodsId = ?",
                    new BeanListHandler<OrderSpec>(OrderSpec.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specs;
    }

    /**
     * 校验和更新订单数据
     * @param update
     * @return
     */
    @Override
    public int changeOrder(OrderUpdate update) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            Order order = runner.query("select * from orders where id = ?",
                    new BeanHandler<>(Order.class), update.getId());
            if ((update.getState() >= 2) && ((update.getSpec() != order.getGoodsDetailId()) || (update.getNum() != order.getGoodsNum()))){
                return 300;
            }
            if (update.getState() < order.getStateId()){
                return 500;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Map<String,Object> map = getUpdateSql(update);
        String sql = (String) map.get("sql");
        List params = (List) map.get("params");
        try {
            runner.update(sql,params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 200;
    }

    /**
     * 获取更新数据的sql语句和里面的参数
     * @param update
     * @return
     */
    private Map<String, Object> getUpdateSql(OrderUpdate update) {
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        String baseSql = "update orders set ";
        baseSql = baseSql + "stateId = ? ,";
        params.add(update.getState());
        baseSql = baseSql + "goodsDetailId = ? ,";
        params.add(update.getSpec());
        baseSql = baseSql + "goodsNum = ? ";
        params.add(update.getNum());
        baseSql = baseSql + "where id = ? ";
        params.add(update.getId());
        map.put("sql",baseSql);
        map.put("params",params);
        return map;
    }

    /**
     * 从orders表中物理删除订单数据
     * @param id
     * @return
     */
    @Override
    public int deleteOrder(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("delete from orders where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 查看订单和购物车
     * @param state
     * @return
     */
    @Override
    public List<Order> getOrderByState(String state) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Order> orderList = null;
        try {
            if (!state.equals("-1")) {
                orderList = runner.query("select * from orders where stateId = ?",
                        new BeanListHandler<Order>(Order.class), state);
            } else {
                orderList = runner.query("select * from orders",
                        new BeanListHandler<Order>(Order.class));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderList;
    }

    /**
     * 通过规格id得到goodsid
     * @param goodsDetailId
     * @return
     */
    @Override
    public Integer getGoodsId(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Integer goodsId = null;
        try {
            goodsId = runner.query("select goodsId from spec where id = ?",
                    new ScalarHandler<>(), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsId;
    }

    /**
     * 通过goodsId得到关于goods的信息
     * @param goodsId
     * @return
     */
    @Override
    public OrderGoods getGoods(Integer goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        OrderGoods goods = null;
        try {
            goods = runner.query("select * from goods where id = ?",
                    new BeanHandler<>(OrderGoods.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    /**
     * 判断该商品是否有评价
     * @param name
     * @return
     */
    @Override
    public Boolean getHasComment(String name) {
        return false;
    }

    /**
     * 向orders表中添加数据
     * @param cart
     * @return
     */
    @Override
    public int addOrder(Cart cart) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Object> params = getParams(cart);
        int row = 0;
        try {
            row = runner.update("insert into orders values(null,?,?,?,?,?,?,?,?,?,?,now())",
                    params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 获取添加到orders表的sql语句
     * @param cart
     * @return
     */
    private List<Object> getParams(Cart cart) {
        List<Object> params = new ArrayList<>();
        params.add(cart.getGoodsDetailId());
        SpecId spec = getSpec(cart.getGoodsDetailId());
        params.add(getGoodsName(spec.getGoodsId()));
        params.add(spec.getSpecName());
        params.add(cart.getNum());
        params.add(cart.getAmount());
        params.add(cart.getState());
        params.add(cart.getToken());
        User user = getUser(cart.getToken());
        params.add(user.getRecipient());
        params.add(user.getAddress());
        params.add(user.getPhone());
        return params;
    }

    /**
     * 通过nickname获取一个user对象
     * @param token
     * @return
     */
    private User getUser(String token) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        User user = null;
        try {
            user = runner.query("select * from user where nickname = ?",
                    new BeanHandler<>(User.class), token);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 通过goodsId获取goodsName
     * @param goodsId
     * @return
     */
    private String getGoodsName(Integer goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String GoodsName = null;
        try {
            GoodsName = runner.query("select name from goods where id = ?",
                    new ScalarHandler<>(), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return GoodsName;
    }

    /**
     * 通过goodsDetailId获取一个spec对象
     * @param goodsDetailId
     * @return
     */
    public SpecId getSpec(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        SpecId spec = null;
        try {
            spec = runner.query("select * from spec where id = ?",
                    new BeanHandler<>(SpecId.class), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return spec;
    }

    /**
     * 更改状态就可完成下单
     * @param cartList
     * @return
     */
    @Override
    public void settleAccounts(CartList cartList) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update orders set stateId = 1 where id = ?",
                    cartList.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int confirmReceive(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("update orders set stateId = 3 where id = ?",
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int pay(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("update orders set stateId = 1 where id = ?",
                    id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
}
